﻿using Dapper;
using Microsoft.Data.SqlClient;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class InventoryInquiryDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public InventoryInquiryDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }
        /// <summary>
        /// 获取出库数量，可用库存，库存总金额
        /// </summary>
        /// <returns></returns>
        public async Task<InventoryInquiry> Inventories()
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                //获取当前总库存和金额
                string sql = @"SELECT 
                    inv.TotalPlannedQuantity,
                    inv.AvailableInventoryQty,
                    inv.PriceAvailableInventoryQty,
                    outb.OutStockQuantity,
                    inbound.InStockQuantity,
                    inbound.UnInStockQuantity
                FROM 
                    -- 库存汇总子查询
                    (
                        SELECT 
                            SUM(wi.total_planned_quantity) AS TotalPlannedQuantity,
                            SUM(wi.available_inventory_qty) AS AvailableInventoryQty,
                            SUM(wi.unit_price * wi.available_inventory_qty) AS PriceAvailableInventoryQty
                        FROM 
                            [dbo].[wh_inventory] wi
                        LEFT JOIN 
                            [dbo].[bi_product] p ON wi.[goods_id] = p.[goods_id]
                    ) inv
                CROSS JOIN 
                    -- 出库汇总子查询
                    (
                        SELECT 
                            SUM(wod.out_stock_quantity) AS OutStockQuantity
                        FROM 
                            [dbo].[wh_outbound] wo
                        LEFT JOIN 
                            [dbo].[wh_outbound_detail] wod ON wod.[stock_out_order_Id] = wo.[stock_out_order_Id]
                        WHERE 
                            wo.[order_status] = '3'
                            AND wod.is_delete = '0'
                    ) outb
                CROSS JOIN 
                    -- 入库汇总子查询
                    (
                        SELECT 
                            SUM(wid.current_stock_in_quantity) AS InStockQuantity,
                            SUM(wid.order_quantity - wid.current_stock_in_quantity) AS UnInStockQuantity
                        FROM 
                            [dbo].[wh_inbound] wi
                        LEFT JOIN 
                            [dbo].[wh_inbound_detail] wid ON wi.stock_order_id = wid.stock_order_id
                        WHERE 
                            wi.is_delete = '0'
                            AND wid.is_delete = '0'
                    ) inbound;";
                return (await con.QueryAsync<InventoryInquiry>(sql)).FirstOrDefault();
            }
        }
        /// <summary>
        /// 产品库存信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProductInventoryInfo>> ProductInventoryInformation()
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"SELECT
	                p.goods_code AS GoodsCode ,
	                p.goods_name AS GoodsName ,
	                CASE p.status WHEN 0 THEN '启用' WHEN 1 THEN '停用' ELSE '未知' END AS Status,
	                c.goods_type_name AS GoodsTypeName ,
	                p.specification AS Specification ,
	                w.warehouse_name AS WarehouseName ,
	                l.storage_location_name AS StorageLocationName ,
	                inv.available_inventory_qty AS CurrentInventory ,
	                inv.unit_price * inv.available_inventory_qty AS InventoryAmount
                FROM
                    dbo.bi_product p
                JOIN
                    dbo.bi_product_category c ON p.goods_type_id = c.goods_type_id
                JOIN
                    dbo.wh_inventory inv ON p.goods_id = inv.goods_id
                JOIN
                    dbo.bi_warehouse w ON inv.warehouse_id = w.warehouse_id
                JOIN
                    dbo.wh_location l ON inv.storage_location_id = l.storage_location_id
                WHERE
                    p.is_delete = '0'
                    AND c.is_delete = '0'
                    AND w.is_delete = '0'";
                return (await con.QueryAsync<ProductInventoryInfo>(sql)).ToList();
            }
        }
    }
}
